package com.houdoudou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.houdoudou.bean.Box;

public class BoxDao {


	 Connection conn=null;	
	public BoxDao(Connection conn) {
		super();
		this.conn = conn;
	}

	public  int create( Box box ) throws SQLException{	
		int result=0;
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		   String sql=null;
	        try {
	        	sql = "insert  into box (machine,id,state,owner) "
	        			+ "values (?,?,?,?)";
	        	pstm=conn.prepareStatement(sql);
	        	pstm.setInt(1, box.getMachine());
	        	pstm.setInt(2, box.getId());
	        	pstm.setInt(3, box.getState());  
	        	pstm.setString(4, box.getOwner());  
				pstm.executeUpdate(); 		
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		return result;
	}
	
	private String getDeleteSql(Box box){
		String sql="delete  from box ";

		int count=0;
		
		if(box.getMachine()!=0) {	
			if(count==0) {sql= sql+" where machine = "+box.getMachine();
			}else{
				sql= sql+" and  sort = "+box.getMachine();
			  }  
			  count++; }
		
		if(box.getId()!=0) {	
			if(count==0) {sql= sql+" where id = "+box.getId();
			}else{
				sql= sql+" and  id = "+box.getId();
			  }  
			  count++; }	
		if(!box.getOwner().isEmpty()) {	
			if(count==0) {sql= sql+" where owner = "+box.getOwner();
			}else{
				sql= sql+" and  owner = "+box.getOwner();
			  }  
			  count++; }
		System.out.print(sql);
		return sql;
	}
	public  void delete(Box box) throws SQLException{	
		
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
		   String sql=null;
	        try {
	        	sql = getDeleteSql(box);		        	
				stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
				stm.executeUpdate(sql);
				
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		
	}
	
	
	
	public  int update( Box box ) throws SQLException{	
		int result=0;
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 		
		// 拼凑 sql.			
	    //sql = "update sort set name=?,display=? where id = ?";
		   try {
			   int whereCount=1;		
				int setCount=1;	
				int count=1;
		String sql="update box  ";	
		if(box.getState()!=-1) {			
			if(setCount==1) {sql= sql+" set state = "+box.getState();
			           }else{sql= sql+" , state =? "+box.getState();}			

			setCount++;   }
		
		
		if(box.getMachine()!=-1) {	
			if(whereCount==1) {sql= sql+" where machine = "+box.getMachine();
			}else{
				sql= sql+" and  sort = "+box.getMachine();}  
			 whereCount++; }
		
		if(box.getId()!=-1) {	
			if(whereCount==1) {sql= sql+" where id = "+box.getId();
			}else{
				sql= sql+" and  id = "+box.getId(); }  
			whereCount++; }
		
		 //---------set-------------
		System.out.print(sql);
		stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
		stm.executeUpdate(sql);
		/*
		if(box.getState()!=-1) {							
			pstm.setInt(count, box.getState());
			count++;  }
		if(box.getOrderID()!=-1) {					
			pstm.setLong(count, box.getOrderID());
			count++;  }
		if(box.getMachine()!=-1) {	
			pstm.setInt(count, box.getMachine());
			count++; }	
		if(box.getId()!=-1) {	
			pstm.setInt(count, box.getId());
			  count++; }
		  	*/
	    //	result=pstm.executeUpdate();
				
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		return result;
	}
	
	 public static  String  getSearchSql(Box box){
			String sql="select * from box";
			int count=0;		
				
			if(box.getMachine()!=-1) {	
				if(count==0) {sql= sql+" where machine = "+box.getMachine();
				}else{
					sql= sql+" and  sort = "+box.getMachine();
				  }  
				  count++; }
			
			if(box.getId()!=-1) {	
				if(count==0) {sql= sql+" where id = "+box.getId();
				}else{
					sql= sql+" and  id = "+box.getId();
				  }  
				  count++; }
			
			if(box.getState()!=-1) {	
				if(count==0) {sql= sql+" where state = "+box.getState();
				}else{
					sql= sql+" and  state = "+box.getState();
				  }  
				  count++; }
			if(!box.getOwner().isEmpty()) {	
				if(count==0) {sql= sql+" where owner = "+box.getOwner();
				}else{
					sql= sql+" and  owner = "+box.getOwner();
				  }  
				  count++; }	
		System.out.println(sql);
			return sql;
		}

	public List< Box> search( String sql) throws SQLException{	
		List< Box> boxes= new ArrayList<Box>();
		  PreparedStatement pstm=null; 
		   Statement  stm=null;
		   ResultSet rs=null; 	
	
	        try {        		        	
				stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
				rs=stm.executeQuery(sql);
				while(rs.next()){
					Box box = new Box();
					box.setId(rs.getInt("id"));
					box.setMachine(rs.getInt("machine"));
					box.setState(rs.getInt("state"));
					box.setOwner(rs.getString("owner"));
					boxes.add(box);
				}
			} finally{
				ConnFactory.closeStatment(pstm, pstm, rs);
			}			
		return boxes;
	}	
	
	 public Box selectById(Box box) throws Exception{
		   String sql="select * from box  where id = "+ box.getId()+" and machine="+box.getMachine();
		   List<Box> boxes= search(sql);
		   Box boxExist = null;
		   if(boxes.size()>0){ boxExist= boxes.get(0); 
		   }
		   return boxExist;
	   }
	
	
	
	
}
